﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;


namespace MarkControle.Models
{
    class BancodeDados
    {







        public int[] ObterContagem(DateTime data)
        {
            int[] result=new int[4];




            string urlConn = "Database=test;Data Source=localhost;User Id=igor;Password=; pooling=false";

            // Create objeto Connection
            MySqlConnection conn = new MySqlConnection(urlConn);
            for (int i = 0; i < 4; i++)
            {
                // Open Connection
                try
                {
                    conn.Open();
                }
                catch (MySqlException e)
                {
                    Console.WriteLine("Error connection by: {0}", e.Message);
                } // end try

                // Validate connection
                if (conn.State.Equals(ConnectionState.Open))
                {

                    try
                    {
                        // Open transaction



                        // Create query e Command
                        StringBuilder sql = new StringBuilder();
                        sql.Append("SELECT * FROM tarefas ");
                        sql.Append("WHERE STATUS=@sta AND PRAZO=@pra");
                        MySqlCommand command = new MySqlCommand(sql.ToString(), conn);


                        command.Parameters.AddWithValue("@pra", data);
                        command.Parameters.AddWithValue("@sta", i);
                        // Add Parameters




                        // Insert customer
                        MySqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {

                            result[i] = result[i] + reader.FieldCount / 10;




                        }
                    }// Commit transaction


                    catch (MySqlException e)
                    {


                    }
                    finally
                    {
                        // Close connection
                        conn.Close();
                    } // end try
                }
            }






            return result;
        }



        public List<tarefa> ObterTarefa(DateTime ini, DateTime fim)
        {
            List<tarefa> listtarefas = new List<tarefa>();

            string urlConn = "Database=markcontrole;Data Source=mysql01.markcontrole.hospedagemdesites.ws;User Id=markcontrole;Password=ivan120869; pooling=false";

            // Create objeto Connection
            MySqlConnection conn = new MySqlConnection(urlConn);

            // Open Connection
            try
            {
                conn.Open();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connection by: {0}", e.Message);
            } // end try

            // Validate connection
            if (conn.State.Equals(ConnectionState.Open))
            {
                
                try
                {
                    // Open transaction

                    

                    // Create query e Command
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT * FROM tarefas ");
                    sql.Append("WHERE PRAZO BETWEEN @ini AND @fim");
                    MySqlCommand command = new MySqlCommand(sql.ToString(), conn);

                    // Add Parameters
                    command.Parameters.AddWithValue("@ini", ini.AddDays(-1));
                    command.Parameters.AddWithValue("@fim", fim);

                    DateTime ike = ini.AddDays(-1);
                    // Insert customer
                    MySqlDataReader reader = command.ExecuteReader();
                    while(reader.Read()){
                        string[] temp = new string[3];
                        int stat=(int)reader["STATUS"];
                        temp[0] = (string)reader["ENCARREGADO1"];
                        temp[1] = (string)reader["ENCARREGADO2"];
                        temp[2] = (string)reader["ENCARREGADO3"];
                        DateTime praz = (DateTime)reader["PRAZO"];
                        tarefa taref = new tarefa((DateTime)reader["PRAZO"], (string)reader["COMENTARIOS"], (string)reader["INFORMACOES"], temp,stat,(DateTime)reader["REQUISICAO"],(int)reader["NAD"],(int)reader["PRIORIDADE"]);
                        if (DateTime.Compare(DateTime.Today.Date, praz.Date) == 1 && stat<=2  )
                        {
                            taref.Status1 = 2;
                            Login bloq = new Login();
                            bloq.Bloqueia(temp[0]);
                            bloq.Bloqueia(temp[1]);
                            bloq.Bloqueia(temp[2]);
                            
                        }

                        listtarefas.Add(taref);
                    }
                    // Commit transaction
                    
                }
                catch (MySqlException e)
                {
                  
                   
                }
                finally
                {
                    // Close connection
                    conn.Close();
                } // end try
            }
            return listtarefas;









        }

        public List<Funcionario> ObterFunci()
        {


            List<Funcionario> listfunci = new List<Funcionario>();

            string urlConn = "Database=markcontrole;Data Source=mysql01.markcontrole.hospedagemdesites.ws;User Id=markcontrole;Password=ivan120869; pooling=false";

            // Create objeto Connectionmysql01.markcontrole.hospedagemdesites.ws
            MySqlConnection conn = new MySqlConnection(urlConn);

            // Open Connection
            try
            {
                conn.Open();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error connection by: {0}", e.Message);
            } // end try

            // Validate connection
            if (conn.State.Equals(ConnectionState.Open))
            {

                try
                {
                    // Open transaction



                    // Create query e Command
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT * FROM funcionario ");
                
                    MySqlCommand command = new MySqlCommand(sql.ToString(), conn);

                    // Add Parameters
             


                    // Insert customer
                    MySqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {

                        Funcionario t = new Funcionario((string)reader["NOME"], (string)reader["USERNAME"], (string)reader["SENHA"], (string)reader["CPF"], (int)reader["BLOQ"], (int)reader["ACESSO"], (string)reader["AREA"]);
                        

                        listfunci.Add(t);
                    }
                    // Commit transaction

                }
                catch (MySqlException e)
                {


                }
                finally
                {
                    // Close connection
                    conn.Close();
                } // end try
            }
            return listfunci;









        }




    }
}
